package com.soms.dao.impl;

import com.soms.dao.RoleDao;
import com.soms.entity.Role;
import com.soms.util.DBUtil;
import com.soms.util.PageInfo;
import com.soms.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class RoleDaoImpl implements RoleDao {

    /**
     * 根据条件查询总数据量
     *
     * @param connection
     * @param params
     * @return
     */
    @Override
    public int queryTotalCountByParams(Connection connection, Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT count(1) FROM `soms_role`");
        List<Object> paramsList = new ArrayList<>();
        // 是否存在WHERE关键字
        boolean hasWhere = false;
        // 有角色编码这个条件
        if (params.containsKey("roleCode") && params.get("roleCode") != null
            && !Objects.equals(params.get("roleCode"), "")) {
            sql.append(" WHERE `role_code` = ?");
            paramsList.add(params.get("roleCode"));
            hasWhere = true;
        }

        // 有角色名称这个条件
        if (params.containsKey("roleName") && params.get("roleName") != null
                && !Objects.equals(params.get("roleName"), "")) {
            sql.append(hasWhere ? " AND" : " WHERE").append(" `role_name` like ?");
            paramsList.add("%" + params.get("roleName") + "%");
            hasWhere = true;
        }

        // 有创建时间起始这个条件
        if (params.containsKey("createdBegin") && params.get("createdBegin") != null
                && !Objects.equals(params.get("createdBegin"), "")) {
            sql.append(hasWhere ? " AND" : " WHERE").append(" `created` > ?");
            paramsList.add(params.get("createdBegin"));
            hasWhere = true;
        }

        // 有创建时间结束这个条件
        if (params.containsKey("createdEnd") && params.get("createdEnd") != null
                && !Objects.equals(params.get("createdEnd"), "")) {
            sql.append(hasWhere ? " AND" : " WHERE").append(" `created` < ?");
            paramsList.add(params.get("createdEnd"));
        }

        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int totalCount = 0;
        try {
            statement = DBUtil.getPreparedStatement(connection, sql.toString(), paramsList.toArray());
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                totalCount = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(resultSet, statement);
        }
        return totalCount;
    }

    /**
     * 根据查询条件和分页信息查询数据集合
     *
     * @param connection
     * @param page
     * @return
     */
    @Override
    public List<Role> queryPage(Connection connection, PageInfo<Role> page) {
        StringBuilder sql = new StringBuilder("SELECT r.*, u1.user_name as creator_name, u2.user_name as modifier_name FROM `soms_role` r LEFT JOIN `soms_user` u1 on r.creator = u1.id LEFT JOIN `soms_user` u2 on r.modifier = u2.id");
        List<Object> paramsList = new ArrayList<>();
        // 是否存在WHERE关键字
        boolean hasWhere = false;
        Map<String, Object> params = page.getParams();
        // 有角色编码这个条件
        if (params.containsKey("roleCode") && params.get("roleCode") != null
                && !Objects.equals(params.get("roleCode"), "")) {
            sql.append(" WHERE `role_code` = ?");
            paramsList.add(params.get("roleCode"));
            hasWhere = true;
        }

        // 有角色名称这个条件
        if (params.containsKey("roleName") && params.get("roleName") != null
                && !Objects.equals(params.get("roleName"), "")) {
            sql.append(hasWhere ? " AND" : " WHERE").append(" `role_name` like ?");
            paramsList.add("%" + params.get("roleName") + "%");
            hasWhere = true;
        }

        // 有创建时间起始这个条件
        if (params.containsKey("createdBegin") && params.get("createdBegin") != null
                && !Objects.equals(params.get("createdBegin"), "")) {
            sql.append(hasWhere ? " AND" : " WHERE").append(" `r`.`created` > ?");
            paramsList.add(params.get("createdBegin"));
            hasWhere = true;
        }

        // 有创建时间结束这个条件
        if (params.containsKey("createdEnd") && params.get("createdEnd") != null
                && !Objects.equals(params.get("createdEnd"), "")) {
            sql.append(hasWhere ? " AND" : " WHERE").append(" `r`.`created` < ?");
            paramsList.add(params.get("createdEnd"));
        }
        // 处理分页
        sql.append(" limit ?, ?");
        paramsList.add((page.getCurrentPage() - 1) * page.getPageSize());
        paramsList.add(page.getPageSize());

        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Role> roleList = new ArrayList<>();
        try {
            statement = DBUtil.getPreparedStatement(connection, sql.toString(), paramsList.toArray());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                // 循环添加
                roleList.add(getRoleByResultSet(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(resultSet, statement);
        }
        return roleList;
    }

    /**
     * 将结果集转换成角色对象
     * @param resultSet
     * @return
     * @throws SQLException
     */
    private Role getRoleByResultSet(ResultSet resultSet) throws SQLException {
        Role role = new Role();
        role.setId(resultSet.getInt("id"));
        role.setRoleCode(StringUtil.transformNull(resultSet.getString("role_code")));
        role.setRoleName(StringUtil.transformNull(resultSet.getString("role_name")));
        role.setCreator(resultSet.getInt("creator"));
        role.setCreatorName(StringUtil.transformNull(resultSet.getString("creator_name")));
        role.setCreated(StringUtil.transformNull(resultSet.getString("created")));
        role.setModifier(resultSet.getInt("modifier"));
        role.setModifierName(StringUtil.transformNull(resultSet.getString("modifier_name")));
        role.setModified(StringUtil.transformNull(resultSet.getString("modified")));
        return role;
    }

    /**
     * 新增一个角色
     *
     * @param connection
     * @param role
     * @return
     */
    @Override
    public int insert(Connection connection, Role role) {
        String sql = "insert into `soms_role` (`role_code`, `role_name`, `creator`, `created`) values(?, ?, ?, ?)";
        Object[] params = {role.getRoleCode(), role.getRoleName(), role.getCreator(), role.getCreated()};
        int result = 0;
        try {
            result = DBUtil.execute(connection, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
            // 只有可能是数据库连不上/SQL语法结构错了
        }
        return result;
    }

    /**
     * 通过id查询单个角色
     *
     * @param connection
     * @param id
     * @return
     */
    @Override
    public Role queryById(Connection connection, int id) {
        Role role = null;
        String sql = "select `role_code`, `role_name` from `soms_role` where `id` = ?";
        Object[] params = {id};
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = DBUtil.getPreparedStatement(connection, sql, params);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                role = new Role(resultSet.getString("role_code"),
                        resultSet.getString("role_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(resultSet, statement);
        }
        return role;
    }

    /**
     * 更新一个角色
     *
     * @param connection
     * @param role
     * @return
     */
    @Override
    public int update(Connection connection, Role role) {
        String sql = "UPDATE `soms_role` SET `role_code` = ?, `role_name` = ?, `modifier` = ?, `modified` = ? WHERE `id` = ?";
        Object[] params = {role.getRoleCode(), role.getRoleName(),
                role.getModifier(), role.getModified(), role.getId()};

        int result = 0;
        try {
            result = DBUtil.execute(connection, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 通过id删除单个角色
     *
     * @param connection
     * @param id
     * @return
     */
    @Override
    public int deleteById(Connection connection, int id) {
        String sql = "DELETE FROM `soms_role` WHERE `id` = ?";
        Object[] params = {id};
        int result = 0;
        try {
            result = DBUtil.execute(connection, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 获取所有的角色
     *
     * @param connection
     * @return
     */
    @Override
    public List<Role> getAllRole(Connection connection) {
        String sql = "SELECT `id`, `role_name` FROM `soms_role`";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Role> roleList = new ArrayList<>();
        try {
            statement = DBUtil.getPreparedStatement(connection, sql, null);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Role role = new Role();
                role.setId(resultSet.getInt("id"));
                role.setRoleName(StringUtil.transformNull(resultSet.getString("role_name")));
                roleList.add(role);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(resultSet, statement);
        }
        return roleList;
    }
}
